﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using Travel_Model;
using System.Data;
using System.Configuration;

namespace Travel_DAL
{
    public class Holiday_DAL
    {
        /// <summary>
        /// 连接数据库
        /// </summary>
        Database db = DatabaseFactory.CreateDatabase("Connection");

        /// <summary>
        /// 显示度假信息  查询
        /// </summary>
        /// <returns></returns>
        public List<holiday> Showholiday(string Playline)
        {
            try
            {
                string sql = "select Holiday.*,[Coitexplain].Coitexplain ,[Ordernotict].Ordernotict,[Visaexplain].Visaexplain,[Refundexplain].Refundexplain,[Elseexplain].Elseexplain from[Coitexplain] left join Holiday on[Coitexplain].Coitexplainid=Holiday.Holidayid left join [Ordernotict] on [Ordernotict].Ordernotictid=Holiday.Holidayid left join [Visaexplain] on [Visaexplain].Visaexplainid=Holiday.Holidayid left join [Refundexplain] on  [Refundexplain].Refundexplainid=Holiday.Holidayid left join [Elseexplain] on[Elseexplain].Elseexplainid=Holiday.Holidayid ";

                if (Playline != null)
                {


                    sql += " where  Playline='" + Playline + "'";
                }
                DbCommand cmd = db.GetSqlStringCommand(sql);

                List<holiday> list = new List<holiday>();

                IDataReader da = db.ExecuteReader(cmd);

                while (da.Read())
                {
                    holiday hd = new holiday();
                    hd.Holidayid = Convert.ToInt32(da["Holidayid"]);
                    hd.Hoildayimg = da["Hoildayimg"].ToString();
                    hd.Hoildaycomdo = da["Hoildaycomdo"].ToString();
                    hd.Hoildayproductid = da["Hoildayproductid"].ToString();
                    hd.Holidayprice = Convert.ToInt32(da["Holidayprice"]);
                    hd.Readnumber = Convert.ToInt32(da["Readnumber"]);
                    hd.Traveldate = Convert.ToDateTime(da["Traveldate"]).ToString("yyyy年MM月dd日");
                    hd.Travelday = Convert.ToInt32(da["Travelday"]);
                    hd.Playline = da["Playline"].ToString();
                    hd.Journeytese = da["Journeytese"].ToString();
                    hd.Coitexplain = da["Coitexplain"].ToString();
                    hd.Ordernotict = da["Ordernotict"].ToString();
                    hd.Visaexplain = da["Visaexplain"].ToString();
                    hd.Refundexplain = da["Refundexplain"].ToString();
                    hd.Elseexplain = da["Elseexplain"].ToString();
                    list.Add(hd);
                }

                return list;
            }
            catch (Exception Erro)
            {
                string Message = Erro.Message;
                throw;
            }
            finally
            { 
            
            }
           
        }

        /// <summary>
        /// 添加度假信息
        /// </summary>
        /// <param name="day"></param>
        /// <returns></returns>
        public int Addholiday(holiday day)
        {
            try
            {
                string sql = "insert into Holiday values(@Hoildayimg,@Hoildaycomdo,@Hoildayproductid,@Holidayprice,@Readnumber,@Traveldate,@Travelday,@Playline,@Journeytese,@Coitexplainid,@Ordernotictid,@Visaexplainid,@Refundexplainid,@Elseexplainid)";
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@Hoildayimg", DbType.String, day.Hoildayimg);
                db.AddInParameter(cmd, "@Hoildaycomdo", DbType.String, day.Hoildaycomdo);
                db.AddInParameter(cmd, "@Hoildayproductid", DbType.String, day.Hoildayproductid);
                db.AddInParameter(cmd, "@Holidayprice", DbType.Decimal, day.Holidayprice);
                db.AddInParameter(cmd, "@Readnumber", DbType.Int32, day.Readnumber);
                db.AddInParameter(cmd, "@Travelday", DbType.Int32, day.Travelday);
                db.AddInParameter(cmd, "@Playline", DbType.String, day.Playline);
                db.AddInParameter(cmd, "@Journeytese", DbType.String, day.Journeytese);
                db.AddInParameter(cmd, "@Coitexplainid", DbType.Int32, day.Coitexplainid);
                db.AddInParameter(cmd, "@Ordernotictid", DbType.Int32, day.Ordernotictid);
                db.AddInParameter(cmd, "@Visaexplainid", DbType.Int32, day.Visaexplainid);
                db.AddInParameter(cmd, "@Refundexplainid", DbType.Int32, day.Refundexplainid);
                db.AddInParameter(cmd, "@Elseexplainid", DbType.Int32, day.Elseexplainid);
                return db.ExecuteNonQuery(cmd);
            }
            catch (Exception Erro)
            {
                string Message = Erro.Message;
                throw;
            }
            finally
            { 
            
            }
           
         

        }

        /// <summary>
        /// 删除度假信息
        /// </summary>
        /// <param name="day"></param>
        /// <returns></returns>
        public int DelHoliday(int id)
        {
            try
            {          
                string sql = "delete from holiday where Holidayid=" + id;
                DbCommand cmd = db.GetSqlStringCommand(sql);
                return db.ExecuteNonQuery(cmd);
            }
            catch (Exception Erro)
            {
                string Message = Erro.Message;
                throw;
            }
            finally {
               
            }


        }


        /// <summary>
        /// 根据id修改行内容行
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<holiday> UpdholidayShow(int id)
        {
            try
            {
                string strSql = "select * holiday where   Holidayid=" + id;
                DbCommand cmd = db.GetSqlStringCommand(strSql);

                List<holiday> list = new List<holiday>();

                IDataReader da = db.ExecuteReader(cmd);

                while (da.Read())
                {
                    holiday hd = new holiday();
                    hd.Holidayid = Convert.ToInt32(da["Holidayid"]);
                    hd.Hoildayimg = da["Hoildayimg"].ToString();
                    hd.Hoildaycomdo = da["Hoildaycomdo"].ToString();
                    hd.Hoildayproductid = da["Hoildayproductid"].ToString();
                    hd.Holidayprice = Convert.ToInt32(da["Holidayprice"]);
                    hd.Readnumber = Convert.ToInt32(da["Readnumber"]);
                    hd.Traveldate = Convert.ToDateTime(da["Traveldate"]).ToString("yyyy年MM月dd日");
                    hd.Travelday = Convert.ToInt32(da["Travelday"]);
                    hd.Playline = da["Playline"].ToString();
                    hd.Journeytese = da["Journeytese"].ToString();
                    hd.Coitexplainid = Convert.ToInt32(da["Coitexplainid"]);
                    hd.Ordernotictid = Convert.ToInt32(da["Ordernotictid"]);
                    hd.Visaexplainid = Convert.ToInt32(da["Visaexplainid"]);
                    hd.Refundexplainid = Convert.ToInt32(da["Refundexplainid"]);
                    hd.Elseexplainid = Convert.ToInt32(da["Elseexplainid"]);
                    list.Add(hd);
                }
                return list;
            }
            catch (Exception Erro)
            {
                string Message = Erro.Message;
                throw;
            }
            finally { 
            
            }

           
        }



        /// <summary>
        /// 修改度假信息
        /// </summary>
        /// <param name="day"></param>
        /// <returns></returns>
        public int UpdHoliday(holiday day)
        {
            try
            {
                string sql = "update holiday set Hoildayimg=@Hoildayimg,Hoildaycomdo=@Hoildaycomdo,Hoildayproductid=@Hoildayproductid,Holidayprice=@Holidayprice,Readnumber=@Readnumber,Traveldate=@Traveldate,Travelday=@Travelday,Playline=@Playline,Journeytese=@Journeytese,Coitexplainid=@Coitexplainid,Ordernotictid=@Ordernotictid,Visaexplainid=@Visaexplainid,Refundexplainid=@Refundexplainid,Elseexplainid=@Elseexplainid where Holidayid='" + day.Holidayid + "'";
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@Holidayid", DbType.Int32, day.Holidayid);
                db.AddInParameter(cmd, "@Hoildayimg", DbType.String, day.Hoildayimg);
                db.AddInParameter(cmd, "@Hoildaycomdo", DbType.String, day.Hoildaycomdo);
                db.AddInParameter(cmd, "@Hoildayproductid", DbType.String, day.Hoildayproductid);
                db.AddInParameter(cmd, "@Holidayprice", DbType.Decimal, day.Holidayprice);
                db.AddInParameter(cmd, "@Readnumber", DbType.Int32, day.Readnumber);
                db.AddInParameter(cmd, "@Travelday", DbType.Int32, day.Travelday);
                db.AddInParameter(cmd, "@Playline", DbType.String, day.Playline);
                db.AddInParameter(cmd, "@Journeytese", DbType.String, day.Journeytese);
                db.AddInParameter(cmd, "@Coitexplainid", DbType.Int32, day.Coitexplainid);
                db.AddInParameter(cmd, "@Ordernotictid", DbType.Int32, day.Ordernotictid);
                db.AddInParameter(cmd, "@Visaexplainid", DbType.Int32, day.Visaexplainid);
                db.AddInParameter(cmd, "@Refundexplainid", DbType.Int32, day.Refundexplainid);
                db.AddInParameter(cmd, "@Elseexplainid", DbType.Int32, day.Elseexplainid);
                return db.ExecuteNonQuery(cmd);

            }
            catch (Exception Erro)
            {
                string Message = Erro.Message;
                throw;
            }
            finally
            { 
            
            }
            

        }



        /// <summary>
        /// 费用说明表
        /// </summary>
        /// <returns></returns>
        public List<Coitexplai> ShowCoitexplain()
        {
            string strSql = "select * from [Coitexplain]";
            DbCommand cmd = db.GetSqlStringCommand(strSql);
            List<Coitexplai> list = new List<Coitexplai>();
            IDataReader rd = db.ExecuteReader(cmd);
            while (rd.Read())
            {
                Coitexplai cp = new Coitexplai();
                cp.Coitexplainid = Convert.ToInt32(rd["Coitexplainid"]);
                cp.Coitexplain = rd["Coitexplain"].ToString();
                list.Add(cp);
            }
            return list;
        }
        /// <summary>
        /// 服务说明表
        /// </summary>
        /// <returns></returns>
        public List<Ordernotict> ShowOrdernotict()
        {
            string strSql = "select * from [Ordernotic]";
            DbCommand cmd = db.GetSqlStringCommand(strSql);
            List<Ordernotict> list = new List<Ordernotict>();
            IDataReader rd = db.ExecuteReader(cmd);
            while (rd.Read())
            {
                Ordernotict cp = new Ordernotict();
                cp.Ordernotictid = Convert.ToInt32(rd["Ordernotictid"]);
                cp.Ordernoticts = rd["Ordernoticts"].ToString();
                list.Add(cp);
            }
            return list;
        }
        /// <summary>
        /// 签证说明表
        /// </summary>
        /// <returns></returns>
        public List<Visaexplain> ShowVisaexplain()
        {
            string strSql = "select * from [Visaexplain]";
            DbCommand cmd = db.GetSqlStringCommand(strSql);
            List<Visaexplain> list = new List<Visaexplain>();
            IDataReader rd = db.ExecuteReader(cmd);
            while (rd.Read())
            {
                Visaexplain cp = new Visaexplain();
                cp.Visaexplainid = Convert.ToInt32(rd["Visaexplainid"]);
                cp.Visaexplains = rd["Visaexplain"].ToString();
                list.Add(cp);
            }
            return list;
        }
        /// <summary>
        /// 违约说明表
        /// </summary>
        /// <returns></returns>
        public List<Refundexplain> ShowRefundexplain()
        {
            string strSql = "select * from [Coitexplain]";
            DbCommand cmd = db.GetSqlStringCommand(strSql);
            List<Refundexplain> list = new List<Refundexplain>();
            IDataReader rd = db.ExecuteReader(cmd);
            while (rd.Read())
            {
                Refundexplain cp = new Refundexplain();
                cp.Refundexplainid = Convert.ToInt32(rd["Refundexplainid"]);
                cp.Refundexplains = rd["Refundexplain"].ToString();
                list.Add(cp);
            }
            return list;
        }


        /// <summary>
        /// 温馨提示说明表
        /// </summary>
        /// <returns></returns>
        public List<Elseexplain> ShowElseexplai()
        {
            string strSql = "select * from [Coitexplain]";
            DbCommand cmd = db.GetSqlStringCommand(strSql);
            List<Elseexplain> list = new List<Elseexplain>();
            IDataReader rd = db.ExecuteReader(cmd);
            while (rd.Read())
            {
                Elseexplain cp = new Elseexplain();
                cp.Elseexplainid = Convert.ToInt32(rd["Elseexplainid"]);
                cp.Elseexplains = rd["Elseexplain"].ToString();
                list.Add(cp);
            }
            return list;
        }

        /// <summary>
        /// 显示度假
        /// </summary>
        /// <returns></returns>
        public List<holiday> Holiday_Show()
        {
            string strSql = "select * from holiday";
            DbCommand cmd = db.GetSqlStringCommand(strSql);
            IDataReader da = db.ExecuteReader(cmd);
            List<holiday> list = new List<holiday>();
            while (da.Read())
            {
                holiday h = new holiday();
                h.Coitexplainid = Convert.ToInt32(da["Coitexplainid"]);
                h.Elseexplainid = Convert.ToInt32(da["Elseexplainid"]);
                h.Hoildaycomdo = da["Holidaycomdo"].ToString();
                h.Hoildayimg = da["Holidayimg"].ToString();
                h.Holidayprice = Convert.ToDecimal(da["Holidayprice"]);
                h.Hoildayproductid = da["Holidayproductid"].ToString();
                h.Holidayid= Convert.ToInt32(da["Holidayid"]);
                h.Journeytese = da["Journeytese"].ToString();
                h.Ordernotictid = Convert.ToInt32(da["Ordernotctid"]);
                h.Playline = da["Playline"].ToString();
                h.Readnumber = Convert.ToInt32(da["Readnumber"]);
                h.Refundexplainid = Convert.ToInt32(da["Refundexplainid"]);
                h.Traveldate = Convert.ToDateTime(da["Traveldate"]).ToString("yyyy/MM/dd");
                h.Travelday = Convert.ToInt32(da["Travelday"]);
                h.Visaexplainid = Convert.ToInt32(da["Visaexplainid"]);
                list.Add(h);
            }
            return list;
        }

        public List<Coitexplai> Coitexplain_Show()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from Coitexplain");
            List<Coitexplai> list = new List<Coitexplai>();
            DataTable table = new DataTable();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                Coitexplai c = new Coitexplai();
                c.Coitexplainid = Convert.ToInt32(item["Coitexplainid"]);
                c.Coitexplain = item["Coitexplain"].ToString();
                list.Add(c);
            }
            return list;
        }
        /// <summary>
        /// 订单须知
        /// </summary>
        /// <returns></returns>
        public List<Ordernotict> Ordernotict_Show()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from Ordernotict");
            List<Ordernotict> list = new List<Ordernotict>();
            DataTable table = new DataTable();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                Ordernotict o = new Ordernotict();
                o.Ordernotictid = Convert.ToInt32(item["Ordernotictid"]);
                o.Ordernoticts = item["Ordernoticts"].ToString();
                list.Add(o);
            }
            return list;
        }
        /// <summary>
        /// 签证说明
        /// </summary>
        /// <returns></returns>
        public List<Visaexplain> Visaexplain_Show()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from Visaexplain");
            List<Visaexplain> list = new List<Visaexplain>();
            DataTable table = new DataTable();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                Visaexplain v = new Visaexplain();
                v.Visaexplainid = Convert.ToInt32(item["Visaexplainid"]);
                v.Visaexplains = item["Visaexplains"].ToString();
                list.Add(v);
            }
            return list;
        }
        /// <summary>
        /// 退款说明
        /// </summary>
        /// <returns></returns>
        public List<Refundexplain> Refundexplain_Show()
        {
            DataSet dt = db.ExecuteDataSet(CommandType.Text, "select * from Refundexplain");
            List<Refundexplain> list = new List<Refundexplain>();
            DataTable table = new DataTable();
            foreach (DataRow item in dt.Tables[0].Rows)
            {
                Refundexplain r = new Refundexplain();
                r.Refundexplainid = Convert.ToInt32(item["Refundexplainid"]);
                r.Refundexplains = item["Refundexplains"].ToString();
                list.Add(r);
            }
            return list;
        }
        /// <summary>
        /// 补充说明
        /// </summary>
        /// <returns></returns>
        public List<Elseexplain> Elseexplain_Show()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from Elseexplain");
            List<Elseexplain> list = new List<Elseexplain>();
            DataTable table = new DataTable();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                Elseexplain e = new Elseexplain();
                e.Elseexplainid = Convert.ToInt32(item["id"]);
                e.Elseexplains = item["Elseexplains"].ToString();
                list.Add(e);
            }
            return list;
        }
        /// <summary>
        /// 度假评分表
        /// </summary>
        /// <returns></returns>
        public List<grade> grade_Show()
        {
            DataSet dt = db.ExecuteDataSet(CommandType.Text, "select * from grade");
            List<grade> list = new List<grade>();
            DataTable table = new DataTable();
            foreach (DataRow item in dt.Tables[0].Rows)
            {
                grade g = new grade();
                g.id = Convert.ToInt32(item["id"]);
                g.Hotelgrade = item["Hotelgrade"].ToString();
                g.hotelid = Convert.ToInt32(item["hotelid"]);
                list.Add(g);
            }
            return list;
        }
        /// <summary>
        /// 游玩人类别显示
        /// </summary>
        /// <returns></returns>
        public List<amuseoneself> amuseoneself_Show()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from amuseoneself");
            List<amuseoneself> list = new List<amuseoneself>();
            DataTable table = new DataTable();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                amuseoneself a = new amuseoneself();
                a.id = Convert.ToInt32(item["id"]);
                a.amuseType = item["amuseType"].ToString();
                a.amuseNum = Convert.ToInt32(item["amuseNum"]);
                a.Holidayid = Convert.ToInt32(item["Holidayid"]);
                list.Add(a);
            }
            return list;
        }
        /// <summary>
        /// 行程介绍
        /// </summary>
        /// <returns></returns>
        public List<journeyreferral> journeyreferral_Show()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from journeyreferral");
            List<journeyreferral> list = new List<journeyreferral>();
            DataTable table = new DataTable();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                journeyreferral j = new journeyreferral();
                j.id = Convert.ToInt32(item["id"]);
                j.Jouneyintroduce = item["Jouneyintroduce"].ToString();
                j.Jouneyregion = item["Jouneyregion"].ToString();
                j.meal = item["meal"].ToString();
                j.Referenceintroduce = item["Referenceintroduce"].ToString();
                j.Trafficvehicle = item["Trafficvehicle"].ToString();
                list.Add(j);
            }
            return list;
        }
        /// <summary>
        /// 行程天数
        /// </summary>
        /// <returns></returns>
        public List<journeyday> journeyday_Show()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from journeyday");
            List<journeyday> list = new List<journeyday>();
            DataTable table = new DataTable();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                journeyday j = new journeyday();
                j.id = Convert.ToInt32(item["id"]);
                j.Jday = item["Jday"].ToString();
                j.Holidayid = Convert.ToInt32(item["Holidayid]"]);
                list.Add(j);
            }
            return list;
        }
    }

}
